﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace FWMS.DAO
{
    class Taizhangguanli
    {
        //库存抽取原料出库明细
        public string kugunguanli_selectchukudate(string pingming, string guige, string beizhu, string month)
        {
            string chukudate = "";
            string sql = "";
            sql = "select chuku_yanshou from chukuguanli where (chuku_type = '3' or chuku_type = '4') and chuku_pinming = '" + pingming.Trim() + "' and chuku_guige = '" + guige +
                  "' and chuku_beizhu = '" + beizhu + "' and Mid(chuku_yanshou,5,2) = '" + month.Trim() + "'";
            object[,] date = (object[,])taizhangguanli_select(sql);
            if (date == null)
            {
                return "2011" + month + "30";
            }
            chukudate = date[0, 0].ToString().Trim();
            return chukudate;
        }

        //台帐取台帐表中的数据
        public bool taizhang_zhuliucheng(string type)
        {
            Bean.KucunguanliBean bean = new FWMS.Bean.KucunguanliBean();
            string sql = "";

            sql = "SELECT DISTINCT rukuguanli.yuanliaoruku_pinming, rukuguanli.ruku_guige, Mid(ruku_yanshou,5,2) AS yanshou," +
                    "(rukuguanli.yuanliaoruku_pinming+rukuguanli.ruku_guige+Mid(ruku_yanshou,5,2)) AS rukudate" +
                    " FROM rukuguanli WHERE ruku_type = '" + type + "' and  (((rukuguanli.[ruku_yanshou])<=(SELECT Max(end_date) AS 表达式1" +
                    " FROM chukumingxijiji where type = '" + type + "'))) and rukuguanli.yuanliaoruku_pinming+rukuguanli.ruku_guige+Mid(ruku_yanshou,5,2) not in" +
                    " (select pinming+guige+Mid(end_date,5,2) as enddate from chukumingxijiji)";
            bean.Cailiaoresult = (object[,])taizhangguanli_select(sql);
            if (bean.Cailiaoresult != null)
            {
                for (int i = 0; i < bean.Cailiaoresult.Length / 4; i++)
                {
                    if ("".Equals(COMMAND.Command.toString(bean.Cailiaoresult[i, 0])))
                    {
                        continue;
                    }
                    else
                    {
                        object[,] result = (object[,])taizhang_getstartenddate(type, COMMAND.Command.toString(bean.Cailiaoresult[i, 2]));
                        if (result == null)
                        {
                            result[0, 0] = System.DateTime.Now.ToString("yyyyMMdd");
                            result[0, 1] = System.DateTime.Now.ToString("yyyyMMdd");
                        }
                        taizhang_mingxiinsert(COMMAND.Command.toString(result[0, 0]), COMMAND.Command.toString(result[0, 1]), COMMAND.Command.toString(bean.Cailiaoresult[i, 0]), COMMAND.Command.toString(bean.Cailiaoresult[i, 1]), type);
                    }
                }
            }


            sql = "SELECT DISTINCT chukumingxijiji.pinming, chukumingxijiji.guige, end_date" +
                    " FROM chukumingxijiji where type = '" + type + "' and pinming+guige+Mid(end_date,5,2) not in ( " +
                    " SELECT DISTINCT (rukuguanli.yuanliaoruku_pinming+rukuguanli.ruku_guige+Mid(ruku_yanshou,5,2)) AS rukudate" +
                    " FROM rukuguanli WHERE (((rukuguanli.[ruku_yanshou])<=(SELECT Max(end_date) AS naxenddate FROM chukumingxijiji))) and ruku_type = '" + type + "' )";
            bean.Cailiaoresult = (object[,])taizhangguanli_select(sql);
            if (bean.Cailiaoresult != null)
            {
                for (int i = 0; i < bean.Cailiaoresult.Length / 3; i++)
                {
                    Bean.KucunguanliBean result = new FWMS.Bean.KucunguanliBean();
                    if ("".Equals(COMMAND.Command.toString(bean.Cailiaoresult[i, 0])))
                    {
                        continue;
                    }
                    else
                    {
                        taizhang_rukuinsert(COMMAND.Command.toString(bean.Cailiaoresult[i, 2]), COMMAND.Command.toString(bean.Cailiaoresult[i, 0]), COMMAND.Command.toString(bean.Cailiaoresult[i, 1]),type);
                    }
                }
            }
            return true;
        }


        //台帐取台帐表中的数据
        public bool taizhang_chanpinzhuliucheng(string type)
        {
            Bean.KucunguanliBean bean = new FWMS.Bean.KucunguanliBean();
            string sql = "";
            string nowdate = System.DateTime.Now.ToString("yyyyMMdd");
            sql = "SELECT DISTINCT rukuguanli.yuanliaoruku_pinming, rukuguanli.ruku_guige FROM rukuguanli WHERE rukuguanli.[ruku_type]='3' and (rukuguanli.yuanliaoruku_pinming+" + " rukuguanli.ruku_guige+mid(ruku_yanshou,5,2))  not in (select chuku_pinming+chuku_guige+mid(chuku_yanshou,5,2) from chukuguanli where chuku_type='3')";
            bean.Cailiaoresult = (object[,])taizhangguanli_select(sql);
            if (bean.Cailiaoresult != null)
            {
                for (int i = 0; i < bean.Cailiaoresult.Length / 2; i++)
                {

                    if ("".Equals(COMMAND.Command.toString(bean.Cailiaoresult[i, 0])))
                    {
                        continue;
                    }
                    else
                    {
                        object[,] result = (object[,])taizhangguanli_select(sql);
                        taizhang_chukuinsert(COMMAND.Command.toString(COMMAND.Command.toString(bean.Cailiaoresult[i, 0])), COMMAND.Command.toString(bean.Cailiaoresult[i, 1]), nowdate,type);
                    }
                }
            }
            sql = "SELECT DISTINCT chukuguanli.chuku_pinming, chukuguanli.chuku_guige FROM chukuguanli" +
                  "  WHERE chuku_type = '3' and (chuku_pinming+chuku_guige+mid(chuku_yanshou,5,2)) not in (  SELECT  rukuguanli.yuanliaoruku_pinming+rukuguanli.ruku_guige+mid(ruku_yanshou,5,2)   FROM rukuguanli WHERE ruku_type = '3' )";
            bean.Cailiaoresult = (object[,])taizhangguanli_select(sql);
            if (bean.Cailiaoresult != null)
            {
                for (int i = 0; i < bean.Cailiaoresult.Length / 2; i++)
                {
                    Bean.KucunguanliBean result = new FWMS.Bean.KucunguanliBean();
                    if ("".Equals(COMMAND.Command.toString(bean.Cailiaoresult[i, 0])))
                    {
                        continue;
                    }
                    else
                    {
                        taizhang_rukuinsert(nowdate, COMMAND.Command.toString(bean.Cailiaoresult[i, 0]), COMMAND.Command.toString(bean.Cailiaoresult[i, 1]),type);
                    }

                }
            }
            return true;
        }
        //查询出库明细启始时间和结束时间
        private object taizhang_getstartenddate(string type, string month)
        {
            Bean.KucunguanliBean bean = new FWMS.Bean.KucunguanliBean();
            string sql = "";
            sql = "SELECT distinct max(start_date), max(end_date) FROM chukumingxijiji WHERE type = '" + type + "'";
            return taizhangguanli_select(sql);
        }
        //出库明细添加0
        private bool taizhang_mingxiinsert(string startdate, string enddate, string pingming, string guige, string type)
        {
            string inserSql = "insert into chukumingxijiji(pinming,guige,shijichukuzhongliang,shijishiyongzhongliang,start_date,end_date,type,save_date) values(";
            inserSql += "'" + pingming + "', ";
            inserSql += "'" + guige + "', ";
            inserSql += "'" + 0 + "', ";
            inserSql += "'" + 0 + "', ";
            inserSql += "'" + startdate + "', ";
            inserSql += "'" + enddate + "', ";
            inserSql += "'" + type + "', ";
            inserSql += "'" + enddate + "') ";
            return taizhangguanli_addmodify(inserSql);
        }
        //出库添加0
        private bool taizhang_chukuinsert(string pingming, string guige, string yanshou, string type)
        {
            string inserSql = "insert into chukuguanli(chuku_pinming,chuku_guige,chuku_jianshu,chuku_zhongliang,chuku_yanshou,chuku_type) values(";
            inserSql += "'" + pingming + "', ";
            inserSql += "'" + guige + "', ";
            inserSql += "'" + 0 + "', ";
            inserSql += "'" + 0 + "', ";
            inserSql += "'" + yanshou + "', ";
            inserSql += "'" + type + "')";
            return taizhangguanli_addmodify(inserSql);
        }
        //入库添加0
        private bool taizhang_rukuinsert(string yanshou, string pingming, string guige, string type)
        {
            string inserSql = "insert into rukuguanli( ruku_type,   ruku_yanshou,  yuanliaoruku_pinming, ruku_guige,  ruku_danzhong, ruku_jianshu, ruku_zhongliang ) values('" +
                type + "','" +
                yanshou + "','" +
                pingming + "','" +
                guige + "','" +
                0 + "','" +
                0 + "','" +
                0 + "')";
            return taizhangguanli_addmodify(inserSql);
        }
        //台帐取库存原料数据
        public Bean.KucunguanliBean taizhangget_kucunyuanliaodata(string type)
        {
            string nowdate = System.DateTime.Now.ToString("yyyyMMdd");
            string nowyear = COMMAND.Command.getyear(nowdate);
            Bean.KucunguanliBean bean = new FWMS.Bean.KucunguanliBean();
            /*
            string sql = "SELECT kucun.pinming, kucun.guige, ruku.ruku_yanshou, ruku.ruku_no, ruku.ruku_fahuodanwei, " +
                          "ruku.ruku_danzhong,ruku.ruku_jianshu, ruku.ruku_zhongliang, mingxi.shijishiyong, kucun.lastmonthzhongliang," +
                          "kucun.thismonthzhongliang,kucun.lastmonthsavedate,kucun.thismonthsavedate,'本月生产用', mingxi.end_date" +
                          "  FROM (kucunguanli AS kucun LEFT JOIN (select * from rukuguanli where ruku_type = '" + type + "' ) AS ruku ON kucun.pinming+kucun.guige=ruku.yuanliaoruku_pinming+ruku.ruku_guige and kucun.cailiao_type = ruku.ruku_type )" +
                          "LEFT JOIN (select pinming,guige,type,start_date,end_date ,sum(shijishiyongzhongliang) as shijishiyong from chukumingxijiji " +
                          " where type ='" + type + "' group by pinming,guige,type,start_date,end_date   ) AS mingxi ON kucun.pinming+kucun.guige=mingxi.pinming+mingxi.guige and kucun.cailiao_type = mingxi.type " +
                          "  WHERE ruku.ruku_yanshou between mingxi.start_date and mingxi.end_date and cailiao_type = '" + type + "' and ruku.ruku_type = '" + type + "' and mingxi.type='" + type +
                          "' and Mid(mingxi.end_date,1,4)=" + nowyear + " ORDER BY kucun.pinming, kucun.guige,ruku.ruku_yanshou";
             * */
            string sql = "SELECT kucun.pinming, kucun.guige, ruku.ruku_yanshou, ruku.ruku_no, ruku.ruku_fahuodanwei, ruku.ruku_danzhong, ruku.ruku_jianshu, ruku.ruku_zhongliang," +
                          " mingxi.shijishiyong, kucun.lastmonthzhongliang, kucun.thismonthzhongliang, kucun.lastmonthsavedate, kucun.thismonthsavedate, '本月生产用' AS 表达式1," +
                          " mingxi.end_date FROM (kucunguanli AS kucun LEFT JOIN (SELECT * FROM rukuguanli WHERE ruku_type='" + type + "')  AS ruku ON (kucun.guige = ruku.ruku_guige) " +
                          " AND (kucun.pinming = ruku.yuanliaoruku_pinming)) LEFT JOIN (SELECT pinming, guige, type, start_date, end_date, sum(shijishiyongzhongliang) AS shijishiyong " +
                          " FROM chukumingxijiji WHERE type='" + type + "' GROUP BY pinming, guige, type,start_date, end_date)  AS mingxi ON (ruku.ruku_guige = mingxi.guige) " +
                          " AND (ruku.yuanliaoruku_pinming = mingxi.pinming) " +
                          " WHERE ruku.ruku_yanshou between mingxi.start_date and mingxi.end_date ORDER BY kucun.pinming,kucun.guige,ruku.ruku_yanshou";
            bean.Cailiaoresult = (object[,])taizhangguanli_select(sql);

            return bean;
        }
        //台帐取库存产品数据
        public Bean.KucunguanliBean taizhangget_kucunchanpin()
        {
            string nowdate = System.DateTime.Now.ToString("yyyyMMdd");
            string nowyear = COMMAND.Command.getyear(nowdate);
            string inityear = nowyear + "0101";
            Bean.KucunguanliBean bean = new FWMS.Bean.KucunguanliBean();
            string sql = "SELECT kucun.pinming, kucun.guige,  ruku.yanshou, '00000', '本月生产', ruku.danzhong, ruku.jianshu, ruku.zhongliang, chuku.chuku_zhongliang," +
                         " kucun.lastmonthzhongliang, kucun.thismonthzhongliang, kucun.lastmonthsavedate, kucun.thismonthsavedate, chuku.chuku_beizhu, chuku.chuku_yanshou" +
                         " FROM (kucunguanli AS kucun LEFT JOIN (SELECT yuanliaoruku_pinming, ruku_guige, ruku_type,mid(ruku_yanshou,5,2) as yanshou," +
                         " sum(ruku_danzhong) AS danzhong,sum(ruku_jianshu) AS jianshu,sum(ruku_zhongliang) AS zhongliang FROM rukuguanli WHERE ruku_type='3' " +
                         " GROUP BY yuanliaoruku_pinming, ruku_guige, ruku_type, mid(ruku_yanshou,5,2))  AS ruku ON (kucun.cailiao_type=ruku.ruku_type) " +
                         " AND (kucun.pinming+kucun.guige=ruku.yuanliaoruku_pinming+ruku.ruku_guige)) LEFT JOIN (SELECT * FROM chukuguanli WHERE chuku_type='3')  AS chuku ON " +
                         " (kucun.cailiao_type=chuku.chuku_type) AND (kucun.pinming+kucun.guige=chuku.chuku_pinming+chuku.chuku_guige) WHERE cailiao_type='3' And ruku.ruku_type='3' " +
                         "  And chuku.chuku_type='3' And ruku.yanshou Between '01' And '" + COMMAND.Command.getmonth(nowdate) + "'  ORDER BY kucun.pinming, kucun.guige, ruku.yanshou";
            bean.Cailiaoresult = (object[,])taizhangguanli_select(sql);

            return bean;
        }
        //执行查询SQL
        private object taizhangguanli_select(string sql)
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    return dbbase.rows;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }
        //执行添加修改SQL
        private bool taizhangguanli_addmodify(string sql)
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (!dbbase.ExecuteSQL(sql.Trim()))
            {
                return false;
            }
            if (dbbase.command())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
